package persistencia;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import dominio.*;

public class RevisorDAO {
	
	public static Revisor newRevisor(String dni) throws Exception {
		Revisor result= new Revisor();
		String[] pclaves=getPclaves(dni);
		result.setKeywords(pclaves);
		String SQL="Select (dni, nombre, apellidos, afiliacion, email) from Persona where dni=?";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, dni);
			ResultSet r = p.executeQuery();
			if (r.next()) {
				result.setDni(r.getString(1));
				result.setNombre(r.getString(2));
				result.setApellido(r.getString(3));
				result.setAfiliacion(r.getString(4));
				result.setEmail(r.getString(5));
			} else {
				throw new Exception("La persona con DNI " + dni + " no existe");
			}			
		} catch (Exception e) {}
		
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
		return result;
	}
	
	private static String[] getPclaves(String dni) throws Exception {
		int a = consultaNum_pclaves(dni);
		String[] result = new String[a];
		String SQL = "Select idPalabra from PalabraRevisor where dni=?";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try{
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			ResultSet r = p.executeQuery();
			for(int i=0; i<a; i++){
        		r.next();
        		result[i]=r.getString("idPalabra");
			}
		}catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return result;
	}

	private static int consultaNum_pclaves(String dni) throws Exception{
		int nuevo = 0;
		String SQL = "SELECT COUNT(*) AS H FROM PalabraRevisor r, Where r.DNI= '"+dni+"'";
		Conexion bd = null;
		Exception ex = null;
		PreparedStatement p = null;
		try{
			bd = Broker.get().getBD();
			p = bd.prepareStatement(SQL);
			p.setString(1, dni);
			ResultSet r = p.executeQuery();
			if (r.next()) {
				nuevo=r.getInt("H");
			} else {
				throw new Exception("El autor con DNI " + dni + " no existe");
			}
		}catch (Exception e) {
			ex = e;
		} finally {
			bd.close();
			if (ex != null)
				throw ex;
		}
		return nuevo;
    }
	
	public static void insert(Revisor revisor) throws Exception {
		PersonaDAO.insert(revisor);
		String SQL="Insert into Revisor (dni) values (?)";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			p.setString(1, revisor.getDni());
			p.executeUpdate();
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
	}
	
	public static void delete(Revisor revisor) throws Exception {
		PersonaDAO.delete(revisor);
	}
	
	public static Vector<Revisor> getRecomendaciones() throws Exception {
		Vector<Revisor> result=new Vector<Revisor>();
		String SQL="Select dni from Revisor";
		Conexion bd=null;
		Exception ex=null;
		PreparedStatement p=null;
		try {
			bd=Broker.get().getBD();
			p=bd.prepareStatement(SQL);
			ResultSet r=p.executeQuery();
			while (r.next()) {
				Revisor revisor= newRevisor(r.getString(1));
				result.add(revisor);
			}			
		}
		catch (Exception e) {
			ex=e;
		}
		finally {
			bd.close();
			if (ex!=null)
				throw ex;
		}
		return result;
	}
	
}
